package com.dbsync.core;

import com.dbsync.exception.DbsyncException;
import com.dbsync.exception.DbsyncUncaughtExceptionHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;

/**
 * SQL执行类
 */
public class SQLExecutor {
    // 日志
    private final static Logger logger = LoggerFactory.getLogger(SQLExecutor.class);
    // 数据库工具对象
    private DBUtil dbUtil;
    // 源数据库所有表缓存
    private List<String> allTables;
    // 任务索引
    private int targetIndex;

    public SQLExecutor(DBUtil dbUtil, int targetIndex) {
        this.dbUtil = dbUtil;
        this.targetIndex = targetIndex;
    }

    /**
     * 获取任务索引
     * @return 返回任务索引
     */
    public int getTargetIndex(){
        return this.targetIndex;
    }

    /**
     * 执行同步任务
     */
    public void excute() throws Exception{
        try{
            long start = System.currentTimeMillis();
            this.dbUtil.init();
            logger.info("*************************************同步操作任务"+targetIndex+"开始*************************************");
            logger.info("该任务url为："+this.dbUtil.getTargets().get(targetIndex-1).getUrl());
            List<String> tables = this.getTables(targetIndex);
            for (String table : tables){
                this.synchronize(table, targetIndex);
                logger.info("同步完成，进行下一张表的同步任务......\n");
            }
            long end = System.currentTimeMillis();
            logger.info("已完成全部表的同步任务，共耗时 "+((end-start)/1000)+" 秒\n");
            logger.info("*************************************同步操作任务"+targetIndex+"结束*************************************");
            logger.info("等待下一次任务执行......");
        }catch (Exception e){
            throw e;
        }
    }

    /**
     * 根据任务索引获取同步表
     * @param index 任务索引
     * @return 返回同步表名称List集合
     * @throws Exception
     */
    private List<String> getTables(int index){
        long start = System.currentTimeMillis();
        logger.info("开始执行表名称查询操作，请稍候......");
        List<String> tables = this.dbUtil.getTargets().get(index-1).getTables();
        if (tables.size()==1&&"allTables".equalsIgnoreCase(tables.get(0))){
            if (this.allTables==null){
                this.allTables = new ArrayList<String>();
                Connection connection = null;
                PreparedStatement preparedStatement = null;
                ResultSet result = null;
                try {
                    connection = this.dbUtil.getDBConnection();
                    preparedStatement = connection.prepareStatement(this.getAllTablesSQL());
                    result = preparedStatement.executeQuery();
                    while (result.next()){
                        this.allTables.add(result.getString(1));
                    }
                    result.close();
                    preparedStatement.close();
                    connection.close();
                }catch (Exception e){
                    logger.info("执行表名称查询操作：失败，失败原因为："+e.getMessage()+"\n");
                    throw new RuntimeException("同步任务出现错误，该任务将停止运行");
                }finally {
                    try {
                        if (result != null){
                            result.close();
                        }
                        if (preparedStatement != null){
                            preparedStatement.close();
                        }
                        if (connection != null){
                            connection.close();
                        }
                    }catch (Exception e){
                        logger.info("执行表名称查询操作：失败，失败原因为："+e.getMessage()+"\n");
                        throw new RuntimeException("同步任务出现错误，该任务将停止运行");
                    }
                }
            }
            long end = System.currentTimeMillis();
            logger.info("执行表名称查询操作： 成功，耗时 "+((end-start)/1000)+" 秒\n");
            return this.allTables;
        }else{
            long end = System.currentTimeMillis();
            logger.info("执行表名称查询操作： 成功，耗时 "+((end-start)/1000)+" 秒\n");
            return tables;
        }
    }

    /**
     * 根据表名获取源数据库对应表数据
     * @param tableName 表名
     * @return 返回对应数据List集合
     * @throws Exception
     */
    private List<Map<String, Object>> getTableContent(String tableName){
        long start = System.currentTimeMillis();
        logger.info("开始执行表("+tableName+")数据查询操作，请稍候......");
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet result = null;
        try {
            connection = this.dbUtil.getDBConnection();
            StringBuilder query = new StringBuilder();
            query.append("SELECT * FROM ").append(tableName);
            preparedStatement = connection.prepareStatement(query.toString());
            result = preparedStatement.executeQuery();
            ResultSetMetaData metaData = result.getMetaData();
            result.last();
            double count = result.getRow();
            result.beforeFirst();
            int percent = 1;
            int index = 1;
            while (result.next()){
                Map<String, Object> data = new HashMap<String, Object>();
                for (int i=1;i<=metaData.getColumnCount();i++){
                    data.put(metaData.getColumnName(i), result.getObject(i));
                }
                list.add(data);
                if (index/count*100>=percent){
                    logger.info("已读取"+(Double.valueOf(index/count*100).intValue())+"%，请稍后......");
                    percent++;
                }
                index++;
            }
        }catch (Exception e){
            logger.info("执行表("+tableName+")数据查询操作：失败，失败原因为："+e.getMessage()+"\n");
            throw new RuntimeException("同步任务出现错误，该任务将停止运行");
        }finally {
            try {
                if (result != null){
                    result.close();
                }
                if (preparedStatement != null){
                    preparedStatement.close();
                }
                if (connection != null){
                    connection.close();
                }
            }catch (Exception e){
                logger.info("执行表("+tableName+")数据查询操作：失败，失败原因为："+e.getMessage()+"\n");
                throw new RuntimeException("同步任务出现错误，该任务将停止运行");
            }
        }
        long end = System.currentTimeMillis();
        logger.info("执行表("+tableName+")数据查询操作： 成功，耗时 "+((end-start)/1000)+" 秒\n");
        return list;
    }

    /**
     * 根据表名与任务索引清理任务数据库对应表数据
     * @param tableName 表名
     * @param index 任务索引
     * @throws Exception
     */
    private void clearTargetTableData(String tableName, int index) throws Exception{
        long start = System.currentTimeMillis();
        logger.info("开始执行清空表("+tableName+")操作，请稍候......");
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try{
            StringBuilder delete = new StringBuilder();
            delete.append("TRUNCATE TABLE ").append(tableName);
            connection = this.dbUtil.getTargetConnection(index);
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(delete.toString());
            preparedStatement.execute();
            connection.commit();
            long end = System.currentTimeMillis();
            logger.info("执行清空表("+tableName+")操作： 成功，耗时 "+((end-start)/1000)+" 秒\n");
        }catch (Exception e){
            try {
                if (connection != null){
                    connection.rollback();
                    connection.commit();
                }
            }catch (Exception e1){
                logger.info("执行清空表("+tableName+")操作： 失败，失败原因为："+e1.getMessage()+"\n");
                throw new RuntimeException("同步任务出现错误，该任务将停止运行");
            }
            logger.info("执行清空表("+tableName+")操作： 失败，失败原因为："+e.getMessage()+"\n");
            throw new RuntimeException("同步任务出现错误，该任务将停止运行");
        }finally {
            try {
                if (preparedStatement!=null){
                    preparedStatement.close();
                }
                if (connection!=null){
                    connection.close();
                }
            }catch (Exception e){
                logger.info("执行清空表("+tableName+")操作： 失败，失败原因为："+e.getMessage()+"\n");
                throw new RuntimeException("同步任务出现错误，该任务将停止运行");
            }
        }
    }

    /**
     * 根据表名与任务索引添加任务数据库对应表数据
     * @param tableName 表名
     * @param index 任务索引
     * @throws Exception
     */
    private boolean addTargetTableData(String tableName, int index) throws Exception{
        List<Map<String, Object>> dataList = this.getTableContent(tableName);
        if (dataList.isEmpty()){
            logger.info("执行同步表("+tableName+")操作： 成功，该表数据为空，跳过\n");
            return true;
        }
        long start = System.currentTimeMillis();
        logger.info("开始执行同步表("+tableName+")操作： ");
        DbsyncUncaughtExceptionHandler exceptionHandler = new DbsyncUncaughtExceptionHandler();
        Thread.setDefaultUncaughtExceptionHandler(exceptionHandler);
        ExecutorService exec = Executors.newCachedThreadPool();
        if (dataList.size() > 1000){
            int count = dataList.size()/4+1;
            for (int i=1;i<=4;i++){
                int counts = count*i;
                exec.execute(new SQLExecutorThead(i, index, this.dbUtil, dataList.subList((i-1)*count, counts<dataList.size()?counts:dataList.size()), tableName, this.logger));
            }
        }else{
            exec.execute(new SQLExecutorThead(1, index, this.dbUtil, dataList, tableName, this.logger));
        }
        exec.shutdown();
        exec.awaitTermination(1, TimeUnit.HOURS);
        long end = System.currentTimeMillis();
        if (exceptionHandler.isError()){
            logger.info("执行同步表("+tableName+")操作： 失败，失败原因为："+exceptionHandler.getReason()+"\n");
            return false;
        }else{
            logger.info("执行同步表("+tableName+")操作： 成功，耗时 "+((end-start)/1000)+" 秒\n");
            return true;
        }
    }

    /**
     * 根据表名与任务索引执行同步任务
     * @param tableName 表名
     * @param index 任务索引
     * @throws Exception
     */
    private void synchronize(String tableName, int index) throws Exception{
        this.clearTargetTableData(tableName, index);
        boolean isNotError = this.addTargetTableData(tableName, index);
        if (!isNotError){
            throw new RuntimeException("同步任务出现错误，该任务将停止运行");
        }
    }

    /**
     * 获取对应数据库类型查询数据sql语句
     * @return 返回sql语句
     * @throws DbsyncException
     */
    private String getAllTablesSQL() throws DbsyncException {
        if ("mysql".equalsIgnoreCase(this.dbUtil.getDBType(0))){
            return "SHOW TABLES";
        }else if ("oracle".equalsIgnoreCase(this.dbUtil.getDBType(0))){
            return "SELECT uo.object_name FROM user_objects uo WHERE uo.object_type = 'TABLE'";
        }else if ("sql server".equalsIgnoreCase(this.dbUtil.getDBType(0))){
            return "SELECT name FROM sysobjects";
        }else if ("sybase".equalsIgnoreCase(this.dbUtil.getDBType(0))){
            return "SELECT name FROM sysobjects WHERE type='U'";
        }else if ("db2".equalsIgnoreCase(this.dbUtil.getDBType(0))){
            return "SELECT tabname FROM syscat.tables WHERE tabschema = current SCHEMA";
        }else{
            return "SHOW TABLES";
        }
    }
}
